In one of my previous articles about the Query Store I briefly touched on the subject of forcing an execution plan via the Query Store. There is, however, much more to relate than I could fit in the previous introduction article. In this article, I’ll be explaining how you can set up a test to find out what happens when you force a plan, both through the Query Store built-in reports and via Query Store’s own DMVs. I’ll then use these to demonstrate what happens when plan-forcing fails, and what is causing the failure.

Forcing execution plans

In my “Forcing Execution Plans using the Query Store” article I described how it was easier to force a specific execution plan than it was to manually build a plan guide: However, I didn’t go into more detail about the way that the Query Store records forced plans or what happens when execution plans can no longer be forced.

There are several ways in which we can force specific execution plans for a query through the Query Store. You have the alternatives of forcing execution plans through the various built-in reports that are provided through the Query Store or by using the sp_query_store_force_plan stored procedure.

In order to get several plans for a single query inside the Query Store, I created a stored procedure that retrieves data from the SalesOrderDetail table based on the ProductID inside the AdventureWorks database.

The next step is to execute the sp_SalesbyProduct stored procedure. I am executing the stored procedure twice with different parameters, 710 and 870. I picked both these parameter values deliberately, basing my choice on how the table data is distributed inside the statistics of the table. Because there are only 44 sales inside the SalesOrderDetail table for Product ID 710, the Query Optimizer resorts to using an ‘Index Seek’ operation. There are a lot more sales for Product ID 870, 4688 to be exact. For such a high number of rows the Query Optimizer chooses to scan the entire table and uses an Index Scan operation.

Between executions of the stored procedure I am forcing a clearance of the plan cache. The reason for this is that SQL Server always prefers to avoid the cost of (re)compilation of execution plans. This is especially true for stored procedures that can easily be parameterized. If I didn’t clear the plan cache between both executions, the first execution would result in a parameterized execution plan inside the plan cache. The second time the stored procedure would be executed the Query Optimizer would detect there already is an execution plan in the plan cache that could be used for the execution of the stored procedure. Even though the execution plan in the plan cache was compiled for a parameter value of 710, SQL Server would execute the exact same plan but then use the parameter value of 870. This way we would never get a different plan based on the parameter value unless we either trigger an execution plan recompilation or remove the plan from the plan cache.

Through the Query Store built-in reports, I can see that two different plans were used when executing the T-SQL above. One of the execution plans uses an Index Seek operation to retrieve the data, whereas the other uses an Index Scan.

Figure 1: different execution plans for the same query

Let’s force one of the plans for the query. In this case I want to force the execution plan that uses an Index Seek operation. From figure 1 above, we can see that our query received a Query Store query ID of 1375, and the execution plan we want to use has an execution plan ID of 1459.

By running the stored procedure above, the execution plan is forced as shown by the little “V” on top of the circle depicting the specific execution plan in figure 2 below.

Figure 2: execution plan 1459 forced on query 1375

So besides showing a “V” on the execution plan inside the graphical Query Store reports, how does the Query Store record what execution plan is forced, and for what query?

Before we were able to use the Query Store, we would query the sys.plan_guides DMV when we were dealing with forced execution plans, so this seems the first logical place to look for forced plans. But, as you can see in figure 3 below, the Query Store doesn’t record information about the forced-plan inside the sys.plan_guides DMV.

Figure 3: sys.plan_guides results

Instead, the Query Store records those plans that are forced inside one of its own DMVs: sys.query_store_plan. By running the following query, we can retrieve all the plans that were forced using the Query Store including some additional information such as the number of occasions that the plan-forcing failed.

Figure 4: forced plan information inside sys.query_store_plan.

To easily identify all the queries that have an execution plan forced, you can also use the (custom) Query Store Database Dashboard. The Query Store Database Dashboard is an open-source and free SSMS report that returns additional information that is stored inside the Query Store, and that isn’t available through the built-in reports. It also includes a section that shows all the forced plans for a database that has the Query Store enabled. You can download the Query Store Database Dashboard through its GitHub page: https://github.com/Evdlaar/QueryStoreDashboard.

Figure 5: Forced Execution Plans section inside the Query Store Database Dashboard

When plan forcing fails

As you have seen in figure 4, the Query Store also records information when it isn’t able to force the execution plan, such as the number of times that it failed to force the plan and the last reason for failure.

As it turns out, there are various reasons why the Query Store is unable to force the execution plan that we want. Let’s take a look at some of those reasons for failure through some examples.

For starters, what would happen if you remove an index that the forced execution plan uses? The plan we forced earlier happens to use the IX_SalesOrderDetail_ProductID index on the SalesOrderDetail table for an Index Seek operation, so let’s disable the index!

We can show this by running our stored procedure again, this time with a product ID of 870. Without plan forcing, this would result in an Index Scan operation and as you can see in figure 6 below we do not get an execution plan forced on the query.

Figure 6:  the old plan instead of the forced plan is returned after disabling the index it used

If we run our query to grab forced plan information from the Query Store we can see if, and why, it failed to force the execution plan.

Figure 7: NO_INDEX execution plan force failure

As you can see in figure 7 above, the Query Store couldn’t force the execution plan because one of the operators inside the execution plan depended on the index we disabled. Hence the NO_INDEX inside the last_force_failure_reason_desc column.

So what happens when we enable our index again? Would the Query Store go back to forcing the execution plan again?

Figure 8: execution plan is forced again

Yes, it does!
Directly after rebuilding the index (a rebuild is required to enable the index) the Query Store is able to force the execution plan again.

Let’s look at another example. What would happen if we were to remove the execution plan we want to force from the Query Store? There are two ways we can use to remove execution plans that are stored inside the Query Store:

• Purge all Query Data
• Remove a single execution plan based on its ID.

If we are to purge the Query Store by using the “Purge Query Data” button on the Query Store properties page or by running the ALTER DATABASE [db_name] SET QUERY_STORE CLEAR command we would remove all data inside the Query Store. This means we don’t just remove all the execution plans and runtime statistics, but we will also remove the plan forcing rules we created. The result is simple, since we removed the plan forcing rules by purging the Query Store nothing gets forced.

We can also choose for a more elegant method, removing a specific execution plan from the Query Store. For this we can use the stored procedure sys.query_store_remove_plan followed with the execution plan ID.

However, if we run the command above we are confronted by the error message shown below in figure 9.

The message is pretty clear, as long as the execution plan is being forced for a query it can only be deleted by purging the entire Query Store.

Next to the example where we removed the index to make the plan forcing fail, there are a few more reasons why an execution plan couldn’t be forced through the Query Store:

Source: https://msdn.microsoft.com/en-us/library/dn818155.aspx

Detecting Query Store plan force failure through Extended Events

A drawback of using the plan force failure information inside the sys.query_store_plan DMV is that you cannot see when a plan force failure occurred. As we have seen before, the Query Store only records the total amount of times a plan force failure occurred and what the last plan force failure was. Thankfully there is an Extended Event we can use to track when a plan force failure occurred and why it occurred.

Let’s set up an Extended Event session to track plan force failures using the example where we removed the index from the table that we are querying.

The Extended Event that we need is called query_store_plan_forcing_failed and it records the error number, message, query ID and the plan ID.

Figure 9: query_store_plan_forcing_failed extended event

To keep things quick and simple, I scripted out the creation of the Extended Event session:

After starting the Extended Event session, I disabled the IX_SalesOrderDetail_ProductID index on the SalesOrderDetail table again and executed the sp_SalesbyProduct stored procedure:

As before, we didn’t get the execution plan we forced through the Query Store. Instead, we see the execution plan with the Index Scan instead of the Index Seek.

Figure 10: Index Scan execution plan is used instead of the forced plan

There is a ring buffer for the session that we created in order to monitor the Query Store plan force failures. If we take a look at the Extended Event data collected inside the ring buffer, we get to see a lot more information than was returned by the Query Store DMV.

Figure 11: query_store_plan_forcing_failed extended event data

As you can see in figure 11 we can see when the extended event triggered, in this case 2016-11-23 at 19:19:06. We can also see the query ID with a value of “1” and the plan ID, also a value of “1” in this case. The error_number of “8712” corresponds with the error description “NO_INDEX” we also see in the sys.query_store_plan DMV. Interestingly enough, the “message” we see recorded in the extended event session indicates we used a plan hint that specified an index that no longer exists. We didn’t use any plan hints inside the stored procedure we used for testing so it seems that this error message is reused.

Summary

In this article we took a look at what happens when you force an execution plan via the Query Store and how you can identify plans that were forced. Through an example we took a look at what happens when the Query Store is unable to force an execution plan and how we can find out why plan-forcing failed through the sys.query_store_plan DMV. Finally, we used an Extended Events session to gather additional information that isn’t available through the Query Store DMVs, including a date/time of the plan force failure and an error message.